/*                                                    `
File:		LcalSql.prg
Version:	1.2
Author:		Alex Antypenko
Description:	Sql commands for (x)Harbour
Notes:		This is the library that contains the implementation mini sql commands

*/

#include "error.ch"
#include "set.ch"
#include "DIRECTRY.CH"
#include "FILEIO.CH"
#include "hbfoxlib.ch"
//#include "hbclass.ch"

#ifdef __XHARBOUR__
 #include "hbcompat.ch"
 #define __LINE__ 0
#else
 #include "xhb.ch"
#endif

MEMVAR __self

****************************************************************
*lsql_CreateIndex(<.uniq.>,<.desc.>,<(cIndexName)>,<(cTableName)>,{ <(bExpr)> })
****************************************************************
Function lsql_CreateIndex(luniq,lDesc,cIndexName,cTableName,cExpr,cForExpr)
    Local _IsCreateIndex:= .F.,lSelect:=.F.,nControl:=0
    LocaL i

    lSelect:=_selectTable(cTableName,.F.)

    If OrdNumber(cIndexName) != 0
		i:=errorNew()
		i:description:="This index is present : "+cIndexName
		i:genCode:=EG_SYNTAX
		i:operation:="CREATE INDEX"
		break(i)
	EndIF	
    _IsCreateIndex:=.T.
    nControl  := CTON( IF( luniq,"1", "0" ) +;
                       IF( EMPTY( cForExpr ), "0", "1" ) +;
                       IF( lDesc,          "1", "0" ), 2 )
    DO CASE
         CASE nControl == 0  // 000 : !lUnique .AND. EMPTY( cFORExpr ) .AND. !lDesCend
            INDEX ON &cExpr TAG &cIndexName  TO &cTableName
         CASE nControl == 1  // 001 : !lUnique .AND. EMPTY( cFORExpr ) .AND. lDesCend
            INDEX ON &cExpr TAG &cIndexName TO &cTableName DESCENDING
         CASE nControl == 2  // 010 : !lUnique .AND. !EMPTY( cFORExpr ) .AND. !lDesCend
            INDEX ON &cExpr TAG &cIndexName TO &cTableName FOR &cFORExpr
         CASE nControl == 3  // 011 : !lUnique .AND. !EMPTY( cFORExpr ) .AND. lDesCend
            INDEX ON &cExpr TAG &cIndexName TO &cTableName FOR &cFORExpr DESCENDING
         CASE nControl == 4  // 100 : lUnique .AND. !EMPTY( cFORExpr ) .AND. !lDesCend
            INDEX ON &cExpr TAG &cIndexName TO &cTableName UNIQUE
         CASE nControl == 5  // 101 : lUnique .AND. !EMPTY( cFORExpr ) .AND. lDesCend
            INDEX ON &cExpr TAG &cIndexName TO &cTableName UNIQUE FOR &cFORExpr DESCENDING
         CASE nControl == 6  // 110 : lUnique .AND. !EMPTY( cFORExpr ) .AND. !lDesCend
            INDEX ON &cExpr TAG &cIndexName TO &cTableName FOR &cFORExpr UNIQUE
         CASE nControl == 7  // 111 : lUnique .AND. !EMPTY( cFORExpr ) .AND. lDesCend
            INDEX ON &cExpr TAG &cIndexName TO &cTableName FOR &cFORExpr UNIQUE DESCENDING
     ENDCASE nControl
 
  
    IF lSelect
	 DbCloseArea()
    EndIf

Return _IsCreateIndex

//
// DROP INDEX <(cTableName)> <(cIndexName)>
//

Function lsql_DropIndex(_cTableName,_cIndexName)
    Local lSelect:=.F.

    lSelect:=_selectTable(_cTableName,.F.)

    OrdDestroy( _cIndexName )

    IF lSelect
	 DbCloseArea()
    EndIf
  
Return Nil
//
// DROP TABLE <(cTableName)> 
//
Function lsql_DropTable(_cTableName)
    Local lSelect:=.F.,_cFileName:=_cTableName
    LocaL i

    if USED(_cTableName) 
      SELECT _cTableName
      USE
    endif
    
    If At("dbf",_cTableName) > 0
     _cTableName:= StrTran( _cTableName, ".dbf" )
    Endif
 
    DeleteFiles("",_cTableName+".*")    // delete all files

    // delete dbf,cdx,ndx,fpt,ntx,idx

    IF lSelect
	 DbCloseArea()
    EndIf
Return Nil
//
// DELETE FROM <(cTableName)> [WHERE <cSearchCondition>]
//	      
Function  lsql_DeleteRows(_cTableName,_cSearchCondition,_bSearchCondition)
    Local _IsDeleteRows:= .F.,lSelect:=.F.
    LocaL i

    lSelect:=_selectTable(_cTableName,.F.)
    // Delete rows
    DBEval( { || dbDelete() }, _bSearchCondition)   
    // 
    IF lSelect
	 DbCloseArea()
    EndIf
Return Nil
//
// ALTER TABLE <(cTableName)> ADD COLUMN <(cColumnName)> <(cDataType)>
//
Function lsql_AlterTableAddCol(_cTableName,_cColumnName,_cDataType)
Return Nil
//
// ALTER TABLE <(cTableName)> DROP COLUMN <(cColumnName)> 
//
Function lsql_AlterTableDropCol(_cTableName,_cColumnName)
Return Nil
//
// ALTER TABLE <(cTableName)> ALTER COLUMN <(cOldColumnName)> <(cNewColumnName)> 
//
Function lsql_AlterTableAlterCol(_cTableName,_cOldColumnName,_cNewColumnName)
Return Nil

//
// SService functions
//
FUNCTION DeleteFiles(cDir,cDeleteShablon)
    LOCAL aDirDeleFile,I

    aDirDeleFile:= Directory( cDir+cDeleteShablon )
    FOR i:=1 TO LEN(aDirDeleFile)
        DELETE FILE ( cDir+aDirDeleFile[i,F_NAME] )
    NEXT
RETURN .T.


//
// Sql Select query functions
//

//CLASS TQuerySelect
//    METHOD New() CONSTRUCTOR
//ENDCLASS

function LocalSqlQueryNew()
    static oSqlQuery
   //PUBLIC _arraySelectOut:={}
   //LOCAL 	oSqlQuery

   if HB_IsNIL(oSqlQuery) //== nil
      oSqlQuery = HBClass():New( "TSQLSELECT" )    // starts a new class definition

      oSqlQuery:AddData( "classname","TSQLSELECT")
      oSqlQuery:AddData( "subQuery", .f.) 			//it`s called as subquery for EXIST,ALL,ANY ?
      oSqlQuery:AddData( "subQueryType",0)   		// EXIST | ANY | ALL | ...
      oSqlQuery:AddData( "externalData",.f.) 		//in query expr exist access for external values
      oSqlQuery:AddData( "fields",{})  				// fields info of result columns
      oSqlQuery:AddData( "All"	,.T.) 				// select ALL or DISCINT
      oSqlQuery:AddData( "allFields", .T.) 			// select ALL or DISCINT for fields list
      oSqlQuery:AddData( "from"	, {} ) 				// tables info
      oSqlQuery:AddData( "into"	, .F.)
      oSqlQuery:AddData( "to"		, 0 )  			// file,printer,screen,rdd,array
      oSqlQuery:AddData( "filename", "")  			// for TO operation
      oSqlQuery:AddData( "hFile"	, NIL) 			// result fopen(::filename)
      oSqlQuery:AddData( "additive", .f.)  			// for TO operation
      oSqlQuery:AddData( "console"	, .T.)
      oSqlQuery:AddData( "plain"	, .F.)
      oSqlQuery:AddData( "wait"	, .T.)
      oSqlQuery:AddData( "where"	, "")
      oSqlQuery:AddData( "filter"	, {||.t.}) 		// codeblock as filter expessions for result table
      oSqlQuery:AddData( "filters"	, NIL   )  		// filter expessions for tables
      oSqlQuery:AddData( "hfilter"	, {||.t.}) 		// codeblock as filter HAVING
      oSqlQuery:AddData( "constants"	, {} )     	// constant value for IN and EXIST,ANY,ALL
      oSqlQuery:AddData( "group"	, "" )
      oSqlQuery:AddData( "groupBlocks" , {})	   	// codeblocks for get value of fields
      oSqlQuery:AddData( "groupKeys"   , {})	   	// groups key data
      oSqlQuery:AddData( "having"	, "" )
      oSqlQuery:AddData( "order"	, NIL )    		// list of column names for ORDER BY
      oSqlQuery:AddData( "functions"	, .f.)  	// have functions in expressions ?
      oSqlQuery:AddData( "gFunctions"	, .f.)  	// have group functions in expressions ? (SUM,COUNT,...)
      oSqlQuery:AddData( "heading"	, .t. ) 		// output header with column names

      oSqlQuery:AddData( "__sets"	, NIL)  		// SET() saved values
      oSqlQuery:AddData( "__havingProcess" , .f.)
      oSqlQuery:AddData( "outBuffer",NIL)  			// buffer for output
      oSqlQuery:AddData( "__curLine",-1 )  			// output lines
      oSqlQuery:AddData( "buffer",{} )  			// buffer for result dataBase or Groups
      oSqlQuery:AddData( "__bufferLine",0)    		// index in buffer for group of current record
	  oSqlQuery:AddData( "arrayOutData",{})	
	  oSqlQuery:AddData( "pArrayOut",NIL)   		// pointer to output array


 	  oSqlQuery:AddMethod("parse	",  @localSql_parse())
 	  oSqlQuery:AddMethod("parseFrom	", @localSql_parseFrom() )
 	  oSqlQuery:AddMethod("parseFields	", @localSql_parseFields() )
 	  oSqlQuery:AddMethod("parseGroup	", @localSql_parseGroup() )
 	  oSqlQuery:AddMethod("parseOrder	", @localSql_parseOrder())
 	  oSqlQuery:AddMethod("parseTo	", @localSql_parseTo())
 	  oSqlQuery:AddMethod("parseExpr	", @localSql_parseExpr() )
 	  oSqlQuery:AddMethod("logicalOptimizeExpr", @localSql_logicalOptimizeExpr() )
 	  oSqlQuery:AddMethod("makeFilters	", @localSql_makeFilters() )
 	  oSqlQuery:AddMethod("openDB	", @localSql_openDB() )
 	  oSqlQuery:AddMethod("run		", @localSql_run() )
 	  oSqlQuery:AddMethod("append	", @localSql_append() )     	// append new result record
 	  oSqlQuery:AddMethod("toBuffer", @localSql_toBuffer() )  		// out to bufferred result record
 	  oSqlQuery:AddMethod("initOut	", @localSql_initOut() )
 	  oSqlQuery:AddMethod("outResult",@localSql_outResult() )
 	  oSqlQuery:AddMethod("newLine	", @dev_newLine() ) 			//CRLF or aadd() to output
 	  oSqlQuery:AddMethod("out	", @dev_out() )
 	  oSqlQuery:AddMethod("closeOut", @localSql_closeOut() )
 	  oSqlQuery:AddMethod("closeDB	", @localSql_closeDB() )
	  oSqlQuery:AddMethod("getArrayData",@localSql_getArrayData())

      //oSqlQuery:AddMethod("destroy	", @localSql_destroy() )
	  oSqlQuery:SetDestructor(@localSql_destroy())
      oSqlQuery:Create()                     						// builds this class
   endif

return oSqlQuery:Instance()                  						// builds an object of this class
**************************************************************
static function localSql_destroy()
	local i
    Local  self:=HB_QSelf() 

	::closeOut()
    ::closeDB()
    for each i in ::constants
    	if "CLASSNAME" $ i .and. i:classname=="TSQLSELECT"
        	i:closeOut()
        	i:closeDB()
        endif
    next
	
	IF ValType(::fields) == "A"
		FOR i:=1 TO LEN(::fields)
			ADEL(::fields,i,.T.)
		NEXT
	Endif

	IF ValType(::outBuffer) == "H"
		FOR i:=1 TO LEN(::outBuffer)
			ADEL(::outBuffer,i,.T.)
			//HaaDelAt( ::outBuffer,i )
		NEXT
	Endif


	//IF ValType(::buffer) == "A"
	//	FOR i:=1 TO LEN(::buffer)
	//		ADEL(::buffer,i,.T.)
	//	NEXT
	//Endif

	//oSqlQuery:=NIL
return Nil
**************************************************************
static function localSql_parse(sQuery)
	    local i,j,k,x,s:="",ch1,ch2,m:={}
        local keyWords:={"from","into","to","noconsole","plain","nowait","where","group by","having","order by"}
        Local  self:=HB_QSelf() 

         // Qout(sQuery)
        // sQuery:=strtran(sQuery,";&\n"," ")
        // sQuery:=strtran(sQuery,"&\t"," ")

        sQuery:= HardCR( sQuery )
        sQuery:=alltrim(sQuery)
		//sQuery:=strtran(sQuery,":",".")
        // Qout(sQuery)

        if lower(substr(sQuery,1,7))=="select "
        	sQuery:=alltrim(substr(sQuery,8))
        else
        	fx_localSql_error("bad expression in SELECT:"+s,__LINE__)
        endif
        s:=lower(sQuery)
        for i=len(keyWords) to 1 step -1
        	k:=1
        	while .t.
        		j:=at(keyWords[i],s,k)
                	if j<=0
                      	exit
                    endif
                	ch1:=substr(s,j-1,1)
                        ch2:=substr(s,j+len(keyWords[i]),1)
                        if ch1==" " .and. (ch2==" " .or. j+len(keyWords[i]) > len(s) )
		        			aadd(m,{keyWords[i],j})
                            exit
                        else
                        	k++
                        endif
                enddo
        next
        if HB_IsNull(m)
        	fx_localSql_error("bad expression in SELECT:"+s,__LINE__)
        endif
        asort(m,,,{|x,y|x[2]<y[2]})
        j:=m[1][2]; s:=m[1][1]
        ::fields:=alltrim(substr(sQuery,1,j-1))

        // ? "ssssssssssss ",len(::fields)," ",::fields 
        // wait
		
        for i=2 to len(m)+1
            x:=iif(i>len(m),len(sQuery)+2,m[i][2])
            do case
            	case s=="from"
                	::from:=substr(sQuery,j+5,x-j-6)
            	case s=="where"
                	::where:=substr(sQuery,j+6,x-j-7)
            	case s=="order by"
                	::order:=substr(sQuery,j+9,x-j-10)
            	case s=="group by"
                	::group:=substr(sQuery,j+9,x-j-10)
            	case s=="to"
                	::to:=substr(sQuery,j+3,x-j-4)
            	case s=="noconsole"
                	::console:=.f.
            	case s=="having"
                	::having:=substr(sQuery,j+7,x-j-8)
            	case s=="nowait"
                	::wait:=.f.
            	case s=="plain"
                	::plain:=.t.
            	case s=="into"
                	::into:=.t.
                	::to:=substr(sQuery,j+5,x-j-4)
            endcase

            if i > len(m)
            	exit
            endif
            j:=m[i][2]; s:=m[i][1]
        next
        if ::into
        	::plain:=.f.
        endif
return Nil
**************************************************************
static function localSql_parseFrom()
	local a,s,i,j,m
    Local  self:=HB_QSelf() 

    if !HB_IsString(::from)
    	return Nil
    endif
    if empty(::from)
    	fx_localSql_error("Sql parse FROM description",__LINE__)
    endif

     //?"::from",::from , " -- "

    s:=alltrim(::from); ::from:={}
    m:=HB_ATokens(s,",")

    //?::from , " -- " ,len(m)

    for i=1 to len(m)
       	s:=alltrim(m[i])
       	a:=Hash()
       	a:hDb:=NIL
       	a:order:=i
       	j:=at(" ",s)
       	if j <= 0
       		a:file:=s
            a:alias:=upper(s)
       	else
       		a:file:=substr(s,1,j-1)
            a:alias:=upper(substr(s,j+1))
       	endif
        j := rat("/",a:alias)
        if j <= 0
        	j := rat("\",a:alias)
        endif
        if j > 0
        	a:alias := substr(a:alias,j+1)
        endif
       	a:order:=len(::from)+1
       	aadd(::from,a)

		 //? "localSql_parseFrom ",a:file," - ",a:alias

    	if ::subQuery  // only first field
        	exit
        endif
    next
return Nil
**************************************************************
static function localSql_parseGroup()
	local a,s,d,x,i,j,m,tmp:={}
        Local  self:=HB_QSelf() 
        if !HB_IsString(::group) .or. empty(::group)
        	return Nil
        endif
        m:=HB_ATokens(alltrim(::group),",")
        if len(m) > 0
        	::groupBlocks:={}
        endif
		//? ":::::::::::::::::group ",::group," len(m) ",len(m);wait
        for i=1 to len(m)
               	s:=alltrim(m[i])
               	a:=Hash()
               	a:hDb:=NIL
               	a:direction:=SQL_DIRECTION_ASC
               	j:=at(".",s)
               	if j<=0
               		a:file:=::from[1]:alias
                   	a:field:=upper(s)
               	else
               		a:file:=alltrim(upper(substr(s,1,j-1)))
                   	a:field:=alltrim(upper(substr(s,j+1)))
               	endif

                d:=val(a:field)
                if d >0
                	if d > len(::fields)
                    	fx_localSql_error("Error number column in GROUP BY",__LINE__)
                    endif
                    a:column := ::fields[d]:xname
                    a:file   := ::fields[d]:aname
                    a:field  := ::fields[d]:fname
                	j:=ascan(::from,{|x|x:alias==a:file})
                	if j<=0
                		fx_localSql_error("Undefine table reference in GROUP BY",__LINE__)
                	endif
                    a:hDB := ::from[j]:hDB
                else
                	j:=ascan(::from,{|x|x:alias==a:file})
                	if j<=0
                		fx_localSql_error("Undefine table reference in GROUP BY",__LINE__)
                	endif
                	d:=::from[j]
                	j:=ascan(d:dbStruct,{|x|x[1]==a:field})
                	if j<=0
	                	fx_localSql_error("Undefine field name in GROUP BY",__LINE__)
        	        endif
                	j:=ascan(::fields,{|x|x:aname==a:file .and. x:fname==a:field})
                	if j>0
                		a:column := ::fields[j]:xname
					endif
					if len(::fields) == 1			
						a:column := ::fields[1]:xname /// ???? error santy
                	endif
                	a:hDb:=d:hdb
                endif
                if i==1 .and. empty(::order)
                	::order:={}
					aadd(::order,a)
                endif

                aadd(tmp,a)
        next
#ifdef DEBUG_GROUP
        ? "group tmp=",tmp
#endif
	
	for each i in tmp
            s:="{|x| ("+(ALLTRIM(str(i:hDb)))+") -> ( FieldGet(FieldPos( '"+i:field+"' )))}"
            aadd(::groupBlocks,&s)
    next
return Nil
**************************************************************
static function localSql_parseOrder()
	local a,s,d,x,i,j,m,tmp:={}
        Local  self:=HB_QSelf()

		//? " order ",::order

        if !HB_IsString(::order) .or. empty(::order)
        	return Nil
        endif
        m:=HB_ATokens(alltrim(::order),",")
        for i=1 to len(m)
               	s:=alltrim(m[i])
               	a:=Hash()
               	j:=at(" ",s)
               	if j<=0
               		a:direction:=SQL_DIRECTION_ASC
                    a:field:=upper(s)
               	else
                	d:=upper(alltrim(substr(s,1,j-1)))
               		a:direction:=iif(d=="DESC",SQL_DIRECTION_DESC,SQL_DIRECTION_ASC)
                    a:field:=alltrim(upper(substr(s,j+1)))
               	endif

                // numeric value of order field
                d:=val(a:field)
                if d >0
                	if d > len(::fields)
                    	fx_localSql_error("Error number column in GROUP BY",__LINE__)
                    endif
                    a:column:=::fields[d]:xname
                else
                	// name table and name field -> table.field
                	j:=at(".",a:field)
               		if j<=0
               			a:file:=::from[1]:alias
                       	a:field:=upper(a:field)
               		else
               			a:file:=alltrim(upper(substr(a:field,1,j-1)))
                       	a:field:=alltrim(upper(substr(a:field,j+1)))
               		endif
                	// search field name in ::fields

					//? "a:field ",a:field
					
                	j:=ascan(::fields,{|x|x:aname==a:file .and. x:xname==a:field})
                    if j<=0
                    	fx_localSql_error("Undefined field name in ORDER BY")
                    endif
                    a:column:=::fields[j]:xname
                endif
                aadd(tmp,a)
        next
		
        /*
        // generate codeblock for asort
		s:="{|x1,x2| "
		for i in tmp
        	s+="x1:"+i:column
            s+=iif(i:direction==SQL_DIRECTION_ASC,"<",">")
            s+="x2:"+i:column
            s+=" .or. "
        next
        s:=substr(s,1,len(s)-6)+"}"
        ::order:=&s
        */
        ::order:=tmp

#ifdef DEBUG_ORDER
        ? "order tmp=",tmp
        //? "codeblock=",s
#endif
return Nil
**************************************************************
static function localSql_parseTo()
	Local i,s,w,fname:=""
    Local  self:=HB_QSelf() 

    ::__sets:=savesets()
    if !HB_IsString(::to)
    	return Nil
    endif
    s:=::to
    ::to:=SQL_TO_NONE
    i:=at(" ",s)
    i:=iif(i <= 0, len(s)+1, i)
    w:=upper(substr(s,1,i-1))  // TO word
    s:=alltrim(substr(s,i+1))
    i:=at(" ",s)
    i:=iif(i <= 0, len(s)+1, i)
    ::filename:=substr(s,1,i-1)     // if exist - filename
    s:=upper(alltrim(substr(s,i+1))) // if exist - additive
    if s=="ADDITIVE"
    	::additive:=.t.
    endif
    do case
    	case w=="FILE"
            	::to:=SQL_TO_FILE
    	case w=="CURSOR"
            	::to:=SQL_TO_CURSOR
    	case w=="DBF"
            	::to:=SQL_TO_DBF
    	case w=="TABLE"
            	::to:=SQL_TO_TABLE
    	case w=="PRINTER"
            	::to:=SQL_TO_PRINTER
    	case w=="ARRAY"
            	::to:=SQL_TO_ARRAY
    	case w=="HASH"
            	::to:=SQL_TO_HASH
    	case w=="OBJECT"
            	::to:=SQL_TO_OBJECT
    endcase
return Nil
**************************************************************
static function localSql_openDB()
	Local i,j
    Local  self:=HB_QSelf() 

    for each i in ::from
    	if i:hDB==NIL
   		        if Select( i:file ) != 0
				    DbSelectArea(Select( i:file ))
					i:Open:=.f.
                    i:hDB := Select(Alias())
                else
					DbUseArea(.T.,,i:file,i:alias)
					i:Open:=.t.				
                    i:hDB := Select(Alias())
                endif
                i:dbstruct:= (i:hDB) ->( DbStruct() )
         endif
    next
return Nil
**************************************************************
static function localSql_closeDB()
	local i
    Local  self:=HB_QSelf() 
        if valtype(::from)=="C"
        	return Nil
        endif
        for each i in ::from
        	if i:hDB!=NIL.and.i:Open
        		(i:hDB)-> ( DbCloseArea() )
                        i:hDB:=NIL
                endif
        next
return Nil
**************************************************************
static function localSql_parseFields()
	local i,j,k,x,y, s, m, fname,aname,nAlias
    local hdb,xname,xnames:=Hash()
    local table
    Local  self:=HB_QSelf() 
	Local _I:=0,_j:=0


        if valtype(::fields)!="C"
        	return Nil
        endif

		::fields:=strtran(::fields,":",".")
		//? "::fields ",::fields        

        s:=alltrim(::fields); ::fields:={}
        if upper(substr(s,1,3))=="ALL"
        	::all:=.t.
			s:=alltrim(substr(s,4))
        endif
        if upper(substr(s,1,8))=="DISTINCT"
        	::all:=.f.
			s:=alltrim(substr(s,9))
        endif
        m:=HB_ATokens(s,",")
        for i=1 to len(m)
        	s:=m[i]
            while NumAt("(",s) != NumAt(")",s)
            	i++
                if i<=len(m)
            	s+=","+ m[i]
                else
                	exit
                endif
            enddo
            s := alltrim(s)
            xname:=""; fname:=""; aname:=""
            if empty(s)
            	loop
            endif
            if "(" $ s  // call functions
                   	::functions:=.t.
                    s:=strtran(s,".",":")
            endif
        	j:=at(" AS ",upper(s))
        	if j>0
        		xname:=alltrim(substr(s,j+4))
                s:=alltrim(substr(s,1,j))
        	endif
            if substr(s,1,1) == "{"
            	s:='dtoc("'+substr(s,2,len(s)-2)+'")'
            endif
            if isdigit(s)
    	       	j:=0
	        else
        		j:=at(".",s)
            endif
        	if j>0
        		aname:=upper(alltrim(substr(s,1,j-1)))
               	fname:=alltrim(substr(s,j+1))
        	else
        		fname:=alltrim(s)
        	endif
			//? "::fields 11111111111 2 ",xname, " | ",s, " | ",aname, " | ",fname
            if !empty(aname)
                   	nAlias:=ascan(::from,{|x|x:alias==aname})
                    if nAlias<=0
        			fx_localSql_error("Alias "+aname+" not found",__LINE__)
                    endif
            else
            	nAlias := 0
            endif
            if fname=="*"
            	if !empty(aname)
                   	y := { ::from[nAlias] }
                else
                   	y := ::from
                endif

	        	for each table in y
					x:=table:dbStruct
                	for j=1 to len(x)
                		xname:=x[j][1]
	            		if ( xname $ xnames ) .and. !::allFields
    	           			loop
	    	        	endif
    	    	    	if ( xname $ xnames )
        	    			xnames[xname][1]++
            			else
		           			xnames[xname]:={1,0}
	     		       	endif
    	        	        k:=Hash()
        	        	    k:xname:=xname
	            	        k:fname:=xname
    	            	    k:aname:=table:alias
        	            	k:len  :=x[j][3]
	        	            k:dec  :=x[j][4]
    	        	        k:order:=len(::fields)+1
        	        		aadd(::fields,k)
	                next
    	    	next
                loop
            endif
            xname:=upper(xname)
        	if empty(xname)
        		xname:=upper(fname)
        	endif
        	if ( xname $ xnames ) .and. !::allFields
           		loop
        	endif
        	if ( xname $ xnames )
        		xnames[xname][1]++
        	else
        		xnames[xname]:={1,0}
        	endif
            fname:=upper(fname)
            //aname:=upper(aname)
            k:=Hash()
            k:xname:=upper(xname)
            k:fname:=fname
            k:aname:=aname
            k:order:=len(::fields)+1
            k:len  :=0
            k:dec  :=0
            aadd(::fields,k)
			//? "::fields 222222222 ",k:xname, " | ",k:aname, " | ",table, " | ",k:fname
        next
        for each i in ::fields      
        	xname:=i:xname
                if empty(i:aname) // default table
                	i:aname:=::from[1]:alias
                endif
                if "(" $ xname
                	loop
                endif
                if xnames[xname][1]>1
                      xname:=substr(xname,1,8)
             	      xname+="_"+chr(64+xnames[i:xname][2]++)
                endif
                i:xname:=xname
        next
        if !( "EXP" $ xnames )
        	xnames:exp:={1,0}
        endif

		//? "::fields 22222 ",len(::fields) 

        for each i in ::fields // check tables, fields, functions
                aname:=i:aname
                table:=ascan(::from,{|x|x:alias==aname})
                if table<=0
                	loop
                endif
				//? "::fields 33333333 ",i:xname, " | ",i:aname, " | ",table, " | ",i:fname
                hdb:=::from[table]:hDB
                if hDB==NIL // error open this alias
                	loop
                endif
				//? "::fields 3______3 ",i:xname, " | ",i:aname, " | ",table, " | ",hdb
                xname:=i:xname
                fname:=i:fname
                m:=::from[table]:dbstruct

                j:=ascan(m,{|x|x[1]==upper(fname)})
				//?  "jjjjjjjjj ",J," | ",LEN(::from[table]:dbstruct)
                if j>0
                	// code block for get field data
                	s:="{|| __localSql_GetValue("+;
                		alltrim(str(hDB))+",'"+fname+"','"+xname+"')}"
                	i:len  :=m[j][3]
                	i:dec  :=m[j][4]
                	i:sblock:=s
                	i:block:=&s
					///santy/ ? "i--block s ",s
                    loop
                endif
                if Upper(xname)==upper(fname)
                	i:xname:="EXP_"+chr(64+xnames:exp[2]++) //fields[i][1]
                else
					i:xname:=xname
                endif
				//? "::fields 3_______311111111 ",i:xname, " | ",i:aname, " | ",table, " | ",i:fname
                if (x:=at("(",fname))>0         // call functions
                	s:=Upper(substr(fname,1,x-1))  // function name
					fname:=substr(fname,x+1, len(fname)-x-1)
                	j:=ascan(m,{|x|x[1]==upper(fname)})
                        if j>0
                			i:len  :=m[j][3]
                			i:dec  :=m[j][4]
                			fname:="__localSql_GetValue("+alltrim(str(hDB))+;
                				",'"+fname+"','"+xname+"')"
                        endif
                        do case
                        	case s=="COUNT"
                                	if "*" $ fname
                                		::gFunctions:=.t.
                                		fname:="__localSql_count(x,y,1)"
										i:len:=10
                                        i:cnt_func:={}
                                        i:enable:=.t.
                						s:="{|x,y| "+fname+"}"
                                        ::groupBlocks:={{||"count"}}
                                    else
                                		::gFunctions:=.t.
                                		fname:="__localSql_count(x,y,"+fname+")"
                                        i:len:=10
                                        i:cnt_func:={} //count
                                        i:enable:=.t.
                						s:="{|x,y| "+fname+"}"
                						//s:="{|x,y| "+fname+"}"
										::groupBlocks:={}
                                    endif
                        	case s=="SUM"
                                	::gFunctions:=.t.
                                	fname:="__localSql_sum(x,y,"+fname+")"
                                    i:len:=max(i:len,16)
                                    i:cnt_func:={}
                                    i:enable:=.t.
                					s:="{|x,y| "+fname+"}"
                        	case s=="MAX"
                                	::gFunctions:=.t.
                                	fname:="__localSql_max(x,y,"+fname+")"
                                    i:len:=MAX(i:len,16)
									i:cnt_func:={}
                                    //i:max:={}  //0-infinity()
                                    i:enable:=.t.
                					s:="{|x,y| "+fname+"}"
									::groupBlocks:={}
                        	case s=="MIN"
                                	::gFunctions:=.t.
                                	fname:="__localSql_min(x,y,"+fname+")"
                                    i:len:=max(i:len,16)
                                    i:cnt_func:={} //infinity()
                                    i:enable:=.t.
                					s:="{|x,y| "+fname+"}"
									::groupBlocks:={}
                        	case s=="AVG"
                                	::gFunctions:=.t.
                                	fname:="__localSql_avg(x,y,"+fname+")"
                                    i:len:=max(i:len,16)
                                    i:cnt_func:={}
                                    i:enable:=.t.
                                    i:count_rec:=0.00
                					s:="{|x,y| "+fname+"}"
									::groupBlocks:={}	
                           otherwise
		        					j:=at(":",upper(fname))
        							if j>0 .AND. ::functions==.t.
										_j:=at("(",upper(fname))
										if _j > 0 
											fname:=alltrim(substr(fname,1,_j-1))+alltrim(substr(fname,j+1))
										else
								 			fname:=alltrim(substr(fname,j+1))
										endif
        							endif
                                	//fname:=s+"("+fname+")"
                					s:="{|| "+s+"("+fname+") }"
                        endcase
                else 				// constant
		        	j:=at(":",upper(fname))
        			if j>0 .AND. ::functions==.t.
        				fname:=alltrim(substr(fname,j+1))
	                    s:="{|| __localSql_GetValue("+;
    	            		alltrim(str(hDB))+",'"+fname+"','"+xname+"')}"
        			else
	                    s:="{|| "+fname+"}"
        			endif
                    //s:="{|| "+fname+"}"
					//? "::fields 55555555555 ",xname, " | ",aname, " | ",table, " | ",fname, " | ",hdb
                endif
				//? "i--block s2 ",s
                i:sblock:=s
                i:block:=&s
        next
return Nil

**************************************************************
**************************************************************
static function localSql_parseExpr(expr)
	local fexit:=.f.,s , ch, word:="",x,y,ret:={}
        local fs1:=.f., fs2:=.f., fs3:=0, fs4:=0
        local opers:=" ( ) < <= = == >= > % ! != # <> $ ' "+' " '
        local words:=" LIKE NOT .NOT. EXIST ALL ANY SOME BETWEEN AND OR .OR. .AND. XOR .XOR. IN "
	    Local  self:=HB_QSelf() 
	    __self:=self 

        if valtype(expr)!="C"
        	return expr
        endif
        if empty(expr)
        	return {}
        endif

		expr:=strtran(expr,":",".")

        s:=alltrim(expr)+" "

#ifdef DEBUG_WHERE
        ? "w_s=",s
#endif
        while   !fexit
        	if empty(s) //.and. empty(word)
                	fexit:=.t.
                endif
        	ch:=substr(s,1,1)
                s:=substr(s,2)
#ifdef DEBUG_WHERE
                ? "w_1:",s,":",ch,":",word,":",ret
#endif
                if ! (ch $ opers) .and. ! (word $ opers ) .and. len(s)!=0
                	word+=ch
                        loop
                endif
#ifdef DEBUG_WHERE
                ? "w_2:",s,":",ch,":",word,":",ret
#endif
                if fs3>0  // function expr
                	word+=ch
                        if ch==")"
                        	fs3 --
                        endif
                        if ch=="("
                        	fs3 ++
                        endif
                        if fs3 == 0
                        	aadd(ret,word)
                                word:=""
                        endif
                       	loop
                endif
                if fs4>0  // sub query "select expr"
                        if ch==")"
                        	fs4--
                        endif
                        if ch=="("
                        	fs4++
                        endif
                        if fs4==0
                        	aadd(ret,word)
                               	word:=""
                        else
                		word+=ch
                        endif
                        loop
                endif
                /* end of string */
                if (fs1 .and. ch=='"') .or. (fs2 .and. ch=="'")
                     	aadd(ret,word+ch)
                        word:=""
                        fs1:=fs2:=.f.
                        if len(s)==0
                        	s:=""
                                fExit:=.f.
                        endif
                        loop
		endif
#ifdef DEBUG_WHERE
                ? "w_31:",s,":",ch,":",word,":",ret
#endif
                /* symbol in string */
                if fs1 .or. fs2
                	word+=ch
                        loop
                endif
#ifdef DEBUG_WHERE
                ? "w_32:",s,":",ch,":",word,":",ret
#endif
		if ch=='"'
       			fs1:=.t.
                        if empty(word)
                        	word:=ch
                        	loop
                        endif
                endif
#ifdef DEBUG_WHERE
                ? "w_33:",s,":",ch,":",word,":",ret
#endif
		if ch=="'"
       			fs2:=.t.
                        word:=ch
                        loop
                endif
#ifdef DEBUG_WHERE
                ? "w_4",":",ch,":",word,":",ret
#endif
                /* space between oper symbols "< =", "< >", "< =", "= =" */
                if !empty(word) .and. !empty(ch) .and. (alltrim(word)+ch $ opers)
                       	word=alltrim(word)+ch
                        if !empty(s)
                        	loop
                        endif
                endif
#ifdef DEBUG_WHERE
                ? "w_5",":",ch,":",'"'+word+'"',":",ret
#endif
                if !empty(word) .and. (word $ opers)
                	x:=0
                    word:=alltrim(word)
                	do case
                        	case word=="="
                                	x:=SQL_OPER_EQUAL
                        	case word=="=="
                                	x:=SQL_OPER_EXACTEQUAL
                        	case word=="!="
                                	x:=SQL_OPER_NOTEQUAL
                        	case word=="<>"
                                	x:=SQL_OPER_NOTEQUAL
                        	case word=="#"
                                	x:=SQL_OPER_NOTEQUAL
                        	case word==">"
                                	x:=SQL_OPER_GREAT
                        	case word==">="
                                	x:=SQL_OPER_GREATEQUAL
                        	case word=="<"
                                	x:=SQL_OPER_LESS
                        	case word=="%"
                                	x:=SQL_OPER_PERCENT
                        	case word=="<="
                                	x:=SQL_OPER_LESSEQUAL
                        	case word=="$"
                                	x:=SQL_OPER_IN
                                        fs4:=1
                        	case word=="("
                                	x:=SQL_OPER_GROUPOPEN
                        	case word==")"
                                	x:=SQL_OPER_GROUPCLOSE
                                otherwise
                                	? "What is it in sql query:",word
                        endcase
                        if x==SQL_OPER_GROUPOPEN .and. !empty(ret)
                        	y=atail(ret)
                                if valtype(y)=="N"
                                	// sub query
                                	if y>=SQL_OPER_EXIST .and. y<=SQL_OPER_SOME ;
                                           .or. y==SQL_OPER_IN
                                        	fs4:=1
                                        	word:=ch
                                        	loop
                                        else  // sub group
		                        	aadd(ret,x)
                                        endif

                                else  // call function
                                     	asize(ret,len(ret)-1)
                                     	word:=y+"("+ch
                                       	fs3 ++
                                        loop
                                endif
			else
                        	aadd(ret,x)
                        endif
                        word:=iif(word==ch,"",ch)
			if ch=='"'
       				fs1:=.t.
                        	word:=ch
                        	loop
                	endif
			if ch=="'"
       				fs2:=.t.
                        	word:=ch
                        	loop
                	endif
                        loop
                endif
#ifdef DEBUG_WHERE
                ? "w_6",":",ch,":",word,":",ret
#endif
		y:=" "+upper(alltrim(word))+" "
                if !empty(y) .and. upper(y) $ words
                	word:=alltrim(upper(word))
                        x:=0
                	do case
                        	case word=="NOT"
                                	x:=SQL_OPER_NOT
                        	case word==".NOT."
                                	x:=SQL_OPER_NOT
                        	case word=="AND"
                                	x:=SQL_OPER_AND
                        	case word==".AND."
                                	x:=SQL_OPER_AND
                        	case word=="OR"
                                	x:=SQL_OPER_OR
                        	case word==".OR."
                                	x:=SQL_OPER_OR
                        	case word=="XOR"
                                	x:=SQL_OPER_XOR
                        	case word==".XOR."
                                	x:=SQL_OPER_XOR
                        	case word=="LIKE"
                                	x:=SQL_OPER_LIKE
                        	case word=="BETWEEN"
                                	x:=SQL_OPER_BETWEEN
                        	case word=="EXIST"
                                	x:=SQL_OPER_EXIST
                        	case word=="ALL"
                                	x:=SQL_OPER_ALL
                        	case word=="ANY"
                                	x:=SQL_OPER_ANY
                        	case word=="SOME"
                                	x:=SQL_OPER_SOME
                        	case word=="IN"
                                	x:=SQL_OPER_IN

                                otherwise
                                	fx_localSql_error("Not supported constructions:"+word,__LINE__)
                        endcase
                        aadd(ret,x)
                        word:=ch
			if ch=='"'
       				fs1:=.t.
                    word:=ch
                    loop
            endif
			if ch=="'"
       			fs2:=.t.
                   	word:=ch
                   	loop
              	endif
                loop
            endif
            if !empty(word)
               	aadd(ret,word)
            endif
#ifdef DEBUG_WHERE
                ? "w_7",":",ch,":",word,":",ret
#endif
                word:=ch
        enddo
#ifdef DEBUG_WHERE
        ? fs1,fs2,fs3,fs4
        ? "where=",len(ret)
        //quit
#endif
        if fs1 .or. fs2 .or. fs3!=0 .or. fs4!=0
        	fx_localSql_error("bad expression in WHERE or HAVING:",__LINE__)
        endif
return ret
**************************************************************
static function localSql_logicalOptimizeExpr(data)
	local i,j,k,x, tags:={},ltags:={}
    local word,aname,fname
    local beg,end,lexit:=.f.
    local lVal,rVal,lOper,rOper
    local onlyAnd,onlyOr
    Local  self:=HB_QSelf() 

#ifdef DEBUG_OPTIMIZE_WHERE
        ? "opt data=",data," | ",Len(data)
		for i=1 to len(data)
			? "data ------ ",data[i]
		endfor
#endif

        for i=1 to len(data)
        	word:=data[i]
                // compare operation
        	if valtype(word)=="N" .and. word==SQL_OPER_BETWEEN
                	if (len(data)-i)<3
                        	fx_localSql_error("Error in BETWEEN",__LINE__)
                        endif
                	if valtype(data[i+2])!="N" .or. ;
                	   data[i+2] != SQL_OPER_AND
                        	fx_localSql_error("Error in BETWEEN",__LINE__)
                        endif
                        lVal:=data[i+1]
                        lVal:=&lVal
                        rVal:=data[i+3]
                        rVal:=&rVal
                        if lVal == rVal
                        	lVal:=rVal:=data[i+1]
                        else
                        	if lVal>rVal
                        		rVal:=data[i+1]
                        		lVal:=data[i+3]
                                else
                        		lVal:=data[i+1]
                        		rVal:=data[i+3]
                                endif
                        endif
                        if lVal==rVal
                     		x:=Hash()
                        	x:type=SQL_TAG_OPER
                        	x:oper:=SQL_OPER_EXACTEQUAL
                        	x:not:=.f.
                        	aadd(tags,x)
                                *
                     		x:=Hash()
                        	x:type=SQL_TAG_CONST
                            x:value:=lVal
                        	x:not:=.f.
                        	aadd(tags,x)
                        else
                        	k:=atail(tags)
                            asize(tags,len(tags)-1)
                     		x:=Hash()
                        	x:type=SQL_TAG_GROUP
                        	x:oper:=SQL_OPER_GROUPOPEN
                        	x:not:=.f.
                        	aadd(tags,x)
                                *
                        	aadd(tags,k)
                                *
                     		x:=Hash()
                        	x:type=SQL_TAG_OPER
                        	x:oper:=SQL_OPER_GREATEQUAL
                        	x:not:=.f.
                        	aadd(tags,x)
                                *
                     		x:=Hash()
                        	x:type=SQL_TAG_CONST
                                x:value:=lVal
                        	x:not:=.f.
                        	aadd(tags,x)
                                *
                     		x:=Hash()
                        	x:type=SQL_TAG_LOGICAL
                            x:oper:=SQL_OPER_AND
                        	x:not:=.f.
                        	aadd(tags,x)
                                *
                        	aadd(tags,k)
                                *
                     		x:=Hash()
                        	x:type=SQL_TAG_OPER
                        	x:oper:=SQL_OPER_LESSEQUAL
                        	x:not:=.f.
                        	aadd(tags,x)
                                *
                     		x:=Hash()
                        	x:type=SQL_TAG_CONST
                            x:value:=rVal
                        	x:not:=.f.
                        	aadd(tags,x)
                                *
                     		x:=Hash()
                        	x:type=SQL_TAG_GROUP
                        	x:oper:=SQL_OPER_GROUPCLOSE
                        	x:not:=.f.
                        	aadd(tags,x)
                        endif
                        i+=3
                	loop
                endif
        	if HB_IsNumeric(word) .and. word<=SQL_OPER_IN
                     	x:=Hash()
                        x:type=SQL_TAG_OPER
                        x:oper:=word
                        x:not:=.f.
                        aadd(tags,x)
                        loop
			endif
                // subquery
        	if HB_IsNumeric(word) .and. word >= SQL_OPER_EXIST .and. word <= SQL_OPER_SOME
                   	if i>=len(data)
                        	fx_localSql_error("bad expression in WHERE:",__LINE__)
                        endif
                     	x:=Hash()
                        x:type=SQL_TAG_OPER
                        x:oper:=word
                        x:not:=.f.
                        aadd(tags,x)
                     	x:=Hash()
                        x:type=SQL_TAG_SUBQUERY
                        x:value:=data[i+1]
                        x:not:=.f.
                        aadd(tags,x)
                        i++  // next tag
                        loop
                endif
                // logical operation
        	if HB_IsNumeric(word) .and. ;
                   (word==SQL_OPER_GROUPOPEN .or. word==SQL_OPER_GROUPCLOSE)
                     	x:=Hash()
                        x:type=SQL_TAG_GROUP
                        x:oper:=word
                        x:not:=.f.
                        aadd(tags,x)
                        loop
                endif
        	if HB_IsNumeric(word)
                     	x:=Hash()
                        x:type=SQL_TAG_LOGICAL
                        x:oper:=word
                        x:not:=.f.
                        aadd(tags,x)
                        loop
                endif
                // call function
        	if HB_IsString(word) .and. "(" $ word
                	::functions:=.t.
                     	x:=Hash()
                        x:type=SQL_TAG_FUNC
                        x:func:=word
                        x:not:=.f.
                        aadd(tags,x)
                        loop
                endif
        		// data for IN,ANY,ALL,SOME
        	if HB_IsString(word)
                	if i>1 .and. HB_IsNumeric(data[i-1])
                		x:=data[i-1]
                        if x>=SQL_OPER_IN .and. x<=SQL_OPER_SOME
                            if x==SQL_OPER_IN .and. "select" $ lower(word)
                            	data[i-1]:=SQL_OPER_ANY
                            endif
                     		x:=Hash()
                        	x:type=SQL_TAG_CONST
                        	x:value:=word
                        	x:not:=.f.
                        	aadd(tags,x)
							// it`s not IN - it`s ANY
                        	loop
                        endif
					endif
                endif
                	// field & table names
        	if HB_IsString(word) .and. "." $ word
                	// check for table.field
                        aname:=""; fname:=""
	                	j:=at(".",word)
                        aname:=alltrim(upper(substr(word,1,j-1)))
                        fname:=alltrim(upper(substr(word,j+1)))
                        // check table in FROM
                        j:=ascan(::from,{|x|x:alias==aname} )
                        if j<=0
                        	fx_localSql_error("bad table reference in WHERE:"+word,__LINE__)
                        endif
                        // check field name in table

						//? "fname ",fname, " i ", i
						//? "::from[j]:dbStruct ",Len(::from[j]:dbStruct)
						
                        k:=ascan(::from[j]:dbStruct,{|x|x[1]==fname})
                        if k<=0
                        	fx_localSql_error("bad table or field reference in WHERE:"+word,__LINE__)
                        endif
                     	x:=Hash()
                        x:type=SQL_TAG_FIELD
                        x:table:=aname
                        x:field:=fname
                        x:hDB:=::from[j]:hDB
                        x:not:=.f.
                        x:xname := NIL
                        for each k in ::fields
                            if k:aname==aname .and. k:fname==fname
                        	   	x:xname := k:xname
                            endif
                        next
                        aadd(tags,x)
                        loop
                endif
                // field name one table name
        	if HB_IsString(word)
            	fname:=alltrim(upper(word))
                        aname:=::from[1]:alias
                        k:=ascan(::from[1]:dbStruct,{|x|x[1]==fname})
						if k>0  // field name one table name
                     		x:=Hash()
                        	x:type=SQL_TAG_FIELD
                        	x:table:=aname
                        	x:field:=fname
                            x:hDB:=::from[1]:hDB
                            x:not:=.f.
                            x:xname := NIL
                            for each k in ::fields
                            	if k:aname==aname .and. k:fname==fname
									x:xname := k:xname
								endif
                            next
                        	aadd(tags,x)
							loop
                        endif
                        // constant
                     	x:=Hash()
                        x:type=SQL_TAG_CONST
                        x:value:=word
                        x:not:=.f.
                        aadd(tags,x)
			endif
        next
#ifdef DEBUG_OPTIMIZE_WHERE
        ? "tags="
        for each i in tags
        	? i
        next
#endif
	// converting NOT and compare operation
	ltags:={}
	for i=1 to len(tags)
        	k:=tags[i]
        	if k:type!=SQL_TAG_OPER
                	aadd(ltags,k)
                	loop
                endif
                lVal:=rVal:=NIL
                if i>1
                	lVal:=tags[i-1]
                else
        		lVal:=Hash(); lVal:type:=SQL_TAG_CONST; lVal:value:=".t."
                endif
                if i<len(tags)
                	rVal:=tags[i+1]
                else
        		rVal:=Hash(); rVal:type:=SQL_TAG_CONST; rVal:value:=".t."
                endif
                if lVal:type>=SQL_TAG_FUNC
                	asize(lTags,len(lTags)-1)
                else
        		lVal:=Hash(); lVal:type:=SQL_TAG_CONST; lVal:value:=".t."
                endif
                if rVal:type>=SQL_TAG_FUNC
                	i++
                else
        		rVal:=Hash(); rVal:type:=SQL_TAG_CONST; rVal:value:=".t."
                endif
                x:=Hash()
                x:type:=SQL_TAG_COMPARE
                x:lvalue:=lVal
                x:rValue:=rVal
                x:oper:=k:oper
                x:not:=.f.
                aadd(lTags,x)
        next
#ifdef DEBUG_OPTIMIZE_WHERE
        ? "ltags="
        for each i in ltags
        	? i
        next
#endif
        // converting logical bulev operation and delete GROUP operation
        lexit:=.f.
        while !lExit
        	lexit:=.t.
        	beg:=end:=j:=x:=0
            onlyAnd:=onlyOr:=.t.
        	for i=1 to len(ltags)
                	k:=lTags[i]
                    if k:type==SQL_TAG_COMPARE
                            if lVal:type==SQL_TAG_CONST
                    			lVal := k:rValue
                    		rVal := k:lValue
                    		k:lValue:=lVal
                    		k:rValue:=rVal
                                    k:not:= !k:not
                            endif
                    endif
                    if k:type==SQL_TAG_LOGICAL .and. k:oper==SQL_OPER_NOT
                    	if i<len(lTags)
                            	if lTags[i+1]:type!=SQL_TAG_GROUP
                            		lTags[i+1]:not:=!(lTags[i+1]:not)
                                    	adel(lTags,i)
                                    	asize(lTags,len(lTags)-1)
                                    	i--
                                    	lExit:=.f.
                                    	loop
                                    endif
                            endif
                    endif
                    if k:type!=SQL_TAG_GROUP
                    	if j!=1
                            	loop
                            endif
                            if k:type!=SQL_TAG_LOGICAL
                            	loop
                            endif
                            if k:oper!=SQL_OPER_NOT
                    		x++
                            endif
                            if k:oper==SQL_OPER_AND
                            	onlyOr:=.f.
                            endif
                            if k:oper==SQL_OPER_OR
                            	onlyAnd:=.f.
                            endif
                    endif
                    if k:oper==SQL_OPER_GROUPCLOSE
                    	j--
                            if j!=0
								loop
                            endif
                            end=i
                            if (end-beg)==2 .or. x<=1
                            	adel(lTags,end)
                            	adel(lTags,beg)
                                    asize(lTags,len(lTags)-2)
                                    i-=2
                                    lExit:=.f.
                            	beg:=end:=j:=x:=0
                            	onlyAnd:=onlyOr:=.t.
                                    loop
                            endif
                            if !onlyAnd .and. !onlyOr
                            	beg:=end:=j:=x:=0
                            	loop
                            endif
                            lOper:=rOper:=iif(onlyAnd,SQL_OPER_OR,SQL_OPER_AND)
                            if beg>1
                            	lOper:=lTags[beg-1]
                                    if lOper:type==SQL_TAG_LOGICAL
                                    	lOper:=lOper:oper
                                    else
                                    	lOper:=SQL_OPER_AND
                                    endif
                            endif
                            if end<len(ltags)
                            	rOper:=lTags[end+1]
                                    if rOper:type==SQL_TAG_LOGICAL
                                    	rOper:=rOper:oper
                                    else
                                    	rOper:=SQL_OPER_AND
                                    endif
                            endif
                            if onlyAnd .and. ;
                              (lOper==SQL_OPER_AND .or. rOper==SQL_OPER_AND)
                            	adel(lTags,end)
                            	adel(lTags,beg)
                                    asize(lTags,len(lTags)-2)
                                    i-=2
                                    lExit:=.f.
                            endif
                            if onlyOR .and. ;
                              (lOper==SQL_OPER_OR .or. rOper==SQL_OPER_OR)
                            	adel(lTags,end)
                            	adel(lTags,beg)
                                    asize(lTags,len(lTags)-2)
                                    i-=2
                                    lExit:=.f.
                            endif
                            onlyAnd:=onlyOr:=.t.
                            beg:=end:=j:=x:=0
                            loop
                    endif
                    if k:oper==SQL_OPER_GROUPOPEN
                    	if beg==0
                    		beg:=i
                            endif
                            j++
                    endif
        	next
        enddo
        *
#ifdef DEBUG_OPTIMIZE_WHERE
        ? "ltags1="
        for each i in ltags
        	? i
        next
#endif
return ltags

**************************************************************
static function localSql_makeFilters(data,onlyMain)
	Local i,j,s,k,x,y,ss,ss1,ss2,fname
    Local ret:={}, lValGet
    Local onlyAnd:=.t.,onlyOR:=.t., functions:=.f.
    Local allInOneTable:=0
    Local lVal,rVal
    Local  self:=HB_QSelf() 		

    *
	// make filter expression for tables
    j:=0
    for each k in data
            if k:type==SQL_TAG_FUNC .and. j==0
            	functions:=.t.
            endif
            if k:type==SQL_TAG_COMPARE .and. j==0
            	if k:lValue:type==SQL_TAG_FUNC
            		functions:=.t.
                endif
            	if k:rValue:type==SQL_TAG_FUNC
            		functions:=.t.
                endif
            endif
            if k:type==SQL_TAG_LOGICAL .and. j==0
            	if k:oper==SQL_OPER_AND
                    	onlyOr:=.f.
                    endif
            	if k:oper==SQL_OPER_OR
                    	onlyAnd:=.f.
                    endif
            endif
            if k:type==SQL_TAG_GROUP
            	if k:oper==SQL_OPER_GROUPOPEN
                    	j++
                    endif
            	if k:oper==SQL_OPER_GROUPCLOSE
                    	j--
                    endif
            endif
    next
#ifdef DEBUG_FILTERS
	? "filter data="
	for each i in data
        	? i
        next
        ? "and=",onlyAnd,"or=",onlyOr, "func=",functions
#endif
        for each i in ::from
        	if onlyOr .and. functions .or. onlyMain
                	aadd(ret,"")
                	exit
                endif
        	j:=0 ; s:=""
        	for each k in data
                	if k:type==SQL_TAG_GROUP
                             	if k:oper==SQL_OPER_GROUPOPEN
                                	j++
                                endif
                             	if k:oper==SQL_OPER_GROUPCLOSE
                                	j--
                                endif
                                loop
                        endif
                        if j>0
                        	loop
                        endif
                        if k:type==SQL_TAG_FIELD
                        	if k:table==i:alias
                                	s+=iif(k:not,"!","")
                                        /*
                        			s+="__localSql_GetValue("+alltrim(str(k:hDB))+;
                            			",'"+k:field+"','"+k:xname+"',x)"+;
                                		iif(onlyAnd," .and. "," .or.  ")
                                        */
                                        s+=k:field
                                        allInOneTable++
                                        loop
                                endif
                        endif
                        if k:type==SQL_TAG_COMPARE
                        	lVal := k:lValue
                        	rVal := k:rValue
                                if rVal:type!=SQL_TAG_CONST .or.;
				   					lVal:type!=SQL_TAG_FIELD
                                   	loop
                                endif
                        	if !(lVal:table==i:alias)
                                	loop
                                endif
                                /*
                                lValGet:="__localSql_GetValue("+alltrim(str(lVal:hDB))+;
                            			",'"+lval:field+"','"+lVal:xname+"',x)"
                                */
                                lValGet:=lVal:field
                                do case
                                	case k:oper==SQL_OPER_EXACTEQUAL
                                		s+=iif(k:not,"!","")+lValGet+"=="
                                        case k:oper==SQL_OPER_EQUAL
                                		s+=lValGet+iif(k:not,"!=","=")
                                        case k:oper==SQL_OPER_NOTEQUAL
                                		s+=lValGet+iif(k:not,"=","!=")
                                        case k:oper==SQL_OPER_GREAT
                                		s+=lValGet+iif(k:not,"<=",">")
                                        case k:oper==SQL_OPER_GREATEQUAL
                                		s+=lValGet+iif(k:not,"<",">=")
                                        case k:oper==SQL_OPER_LESS
                                		s+=lValGet+iif(k:not,">=","<")
                                        case k:oper==SQL_OPER_LESSEQUAL
                                		s+=lValGet+iif(k:not,">","<=")
                                        otherwise
                                        	loop
                                endcase
                                allInOneTable++
                                s+=rVal:value //+iif(onlyAnd," .and. ","  .or. ")
                        endif
                        if !empty(s)
                		if k:oper == SQL_OPER_AND
                			s+=" .and. "
                		endif
                		if k:oper == SQL_OPER_OR
                			s+=" .or.  "
                		endif
                        endif
                next
                //s:=substr(s,1,len(s)-7)
                aadd(ret,s)
#ifdef DEBUG_FILTERS
                ? "filter",i:alias,"set filter=",s
#endif
        next
        allInOneTable := (len(::from)==1 .and. allInOneTable==len(data))
        /*
        if allInOneTable .and. !onlyMain
        	ret:={}
                asize(ret,len(::from)+1)
                afill(ret,"")
                ret[len(ret)]:={||.t.}
        	return ret
        endif
        */
        *
        // make codeblock as filter for result table
        s:=""
        for each k in data
        	if k:type==SQL_TAG_GROUP
                     	if k:oper==SQL_OPER_GROUPOPEN
                        	s+="("
                        endif
                     	if k:oper==SQL_OPER_GROUPCLOSE
                        	s+=")"
                        endif
                        loop
                endif
                if k:type==SQL_TAG_FIELD
                       	s+=iif(k:not,"!","")
                        s+="__localSql_GetValue("+alltrim(str(k:hDB))+;
                            ",'"+k:field+"','"+iif(k:xname==NIL,"",k:xname)+"',x)"+;
                       	    iif(onlyAnd," .and. "," .or.  ")
                        loop
                endif

                if k:type==SQL_TAG_COMPARE
                	lVal := k:lValue
                	rVal := k:rValue
                        ss:=""; ss1:=""; ss2:=""
                	if lVal:type==SQL_TAG_FIELD
                        	ss1:="__localSql_GetValue("+alltrim(str(lVal:hDB))+;
                            	   ",'"+lVal:field+"','"+iif(lVal:xname==NIL,"",lVal:xname)+"',x)"
                        endif
                	if lVal:type==SQL_TAG_CONST
						ss1=lVal:value
                	endif
                	if lVal:type==SQL_TAG_FUNC
						ss1=lVal:func
                        	fname:=upper(substr(ss1,1,at("(",ss1)-1))
                                if fname $ "COUNT SUM AVG MAX MIN"
									for each j in ::fields
                                    	if upper(ss1)==upper(j:fname)
                                         	ss1:='__localSql_getValue(NIL,NIL,"'+j:xname+'",x)'
                                            exit
                                     	endif
                                    next
                                endif
                	endif
                	if rVal:type==SQL_TAG_FIELD
                        	ss2="__localSql_GetValue("+alltrim(str(rVal:hDB))+;
                            	   ",'"+rVal:field+"','"+iif(rVal:xname==NIL,"",rVal:xname)+"',x)"
                        else // SQL_TAG_CONST
							ss2=rVal:value
                	endif
                        do case
                        	case k:oper==SQL_OPER_EXACTEQUAL
                        		ss=iif(k:not,"!","")+ss1+"=="+ss2
                                case k:oper==SQL_OPER_EQUAL
                        		ss=ss1+iif(k:not,"!=","=")+ss2
                                case k:oper==SQL_OPER_NOTEQUAL
                        		ss=ss1+iif(k:not,"=","!=")+ss2
                                case k:oper==SQL_OPER_GREAT
                        		ss=ss1+iif(k:not,"<=",">")+ss2
                                case k:oper==SQL_OPER_GREATEQUAL
                        		ss=ss1+iif(k:not,"<",">=")+ss2
                                case k:oper==SQL_OPER_LESS
                        		ss=ss1+iif(k:not,">=","<")+ss2
                        	case k:oper==SQL_OPER_PERCENT
                        		ss=iif(k:not,"!","")+ss1+"%"+ss2
                                case k:oper==SQL_OPER_LESSEQUAL
                        		ss=ss1+iif(k:not,">","<=")+ss2
							case k:oper==SQL_OPER_LIKE
                        		ss=iif(k:not,"!","")+"search("+ss2+","+ss1+")"
							case k:oper==SQL_OPER_IN
                                	// make constant
                                	x:=HB_ATokens(rVal:value,",")
                                        y:=Hash()
                                        for j=1 to len(x)
                                        	x[j]:=&(x[j])
                                                y[ x[j] ]:=x[j]
                                        next
                                        aadd(::constants,y)
                        		//ss=iif(k:not,"!","")+ss1+" $ __self:constants["+alltrim(str(len(::constants)))+"]"
                        		ss=iif(k:not,"!","")+"__localSql_run_subQuery(__self:constants["+alltrim(str(len(::constants)))+"],"+ss1+")"
							case k:oper==SQL_OPER_EXIST
                                	y:=makeSqlSubSelect(SQL_OPER_EXIST,ss2,ss1)
                                        aadd(::constants,y)
                        		ss=iif(k:not,"!","")+"__localSql_run_subQuery(__self:constants["+alltrim(str(len(::constants)))+"],"+ss1+")"
							case k:oper==SQL_OPER_ALL
                                	y:=makeSqlSubSelect(SQL_OPER_ALL,ss2,ss1)
                                        aadd(::constants,y)
                        		ss=iif(k:not,"!","")+"__localSql_run_subQuery(__self:constants["+alltrim(str(len(::constants)))+"],"+ss1+")"
							case k:oper==SQL_OPER_ANY
                                	y:=makeSqlSubSelect(SQL_OPER_ANY,ss2,ss1)
                                        aadd(::constants,y)
                        		ss=iif(k:not,"!","")+"__localSql_run_subQuery(__self:constants["+alltrim(str(len(::constants)))+"],"+ss1+")"
							case k:oper==SQL_OPER_SOME
                                	y:=makeSqlSubSelect(SQL_OPER_SOME,ss2,ss1)
                                        aadd(::constants,y)
                        		ss=iif(k:not,"!","")+"__localSql_run_subQuery(__self:constants["+alltrim(str(len(::constants)))+"],"+ss1+")"
                                otherwise
                                /*
                                	error ???
                                // between converted to AND
							case k:oper==SQL_OPER_NOTBETWEEN
							case k:oper==SQL_OPER_BETWEEN
                                */
                        endcase
                        s+=ss//+iif(onlyAnd .or. k:oper==SQL_OPER_AND," .and. ","  .or. ")
                endif
                if "OPER" $ k
                	if k:oper == SQL_OPER_AND
                		s+=" .and. "
                	endif
                	if k:oper == SQL_OPER_OR
                		s+=" .or.  "
                	endif
                endif
                //s+=iif(onlyAnd .or. k:oper==SQL_OPER_AND," .and. ","  .or. ")
        next
        //s:=substr(s,1,len(s)-7)
        if empty(s)
        	s:="{|| .t. }"
        else
        	s:="{|x,y|"+s+"}"
        endif
        aadd(ret,&s)
#ifdef DEBUG_FILTERS
        ? "filter for result table=",s
#endif

return ret

**************************************************************
**************************************************************
**************************************************************
static function localSql_initOut()
	Local i,_j,ii,iii,aname,data,fname,mstru:={}
    Local s,_fldname:="",_fldname1:="",_vartmp:=""
    Local  self:=HB_QSelf(), _arrayData:=""
	Local i1:=0 		

        i:=::to
        if i==SQL_TO_CURSOR .or. i==SQL_TO_DBF
        	for ii=1 to len(::from)
        		aname:=::from[ii]
                (aname:hDb)->( DbGotop() ) 
               	s:=aname:dbStruct
                _fldname := aname:file     
               	private &_fldname
                for each iii in s
					_fldname1:= iii[1]
		          	PRIVATE &_fldname1
                    __mvPut(_fldname1,fieldGet(FieldPos( _fldname1 )))
					
					///*/?  "localSql_initOut ",_fldname1," | ",fieldGet(FieldPos( _fldname1 ))

                next
        	next
        	for each i in ::fields
                //if hb_HHASKEY(i,"block")              
					data:=eval(i:block)
    	   	        i:len := max(i:len,IIF(HB_IsNumeric(data),lenNum(data),len(data) ))
					_vartmp := IIF(HB_IsNumeric(data),STR( data ),data)
					i:dec := IIF('.' $ _vartmp,MAX(i:dec,LEN(_vartmp)-AT( '.', _vartmp )),0)
            	    //i:dec := 2  //max(i:dec,dec(data))
             		aadd(mstru,{i:xname,valtype(data),i:len,i:dec})
				//endif
        	next
        endif
        set console (::console)
		do case
            case ::to==SQL_TO_NONE
                    ::outBuffer:=""
        	case ::to==SQL_TO_PRINTER
                	set device to printer
                    set printer on
                    ::outBuffer:=""
        	case ::to==SQL_TO_FILE
    				_j:=at(":",upper(::filename))
        			if _j>0
						::filename:=strtran(::filename,":",".")	
                    EndIf
					
                	if ::additive .and. file(::filename)
                		::hfile:=fopen(::filename,FO_WRITE)
                    else
                      	::hfile:=fcreate(::filename,FC_NORMAL)
                    endif
                    if ::hfile<0
                       	fx_localSql_error("Cannot open output file:"+::filename,__LINE__)
                    endif
                    if ::additive
                       	fseek(::hFile,0,FS_END)
                    endif
			 	    __objModMethod( self, "newLine	", @file_newLine() ) 
				    __objModMethod( self,"out	", @file_out() )
                    ::heading:=.f.
                    ::outBuffer:=""
        	case ::to==SQL_TO_CURSOR
                	fname:=tmpfile()
                    dbcreate(fname,mstru)
                    use (fname) alias (::fileName) exclusive new
			 	    __objModMethod( self, "newLine	", @tbl_newLine() ) 
				    __objModMethod( self,"out	", @tbl_out() )
                    ::heading:=.f.
                    ::outBuffer:=Hash()
        	case ::to==SQL_TO_DBF
                    dbcreate(::filename,mstru)
                    use (::filename) new
			 	    __objModMethod( self, "newLine	", @tbl_newLine() ) 
				    __objModMethod( self,"out	", @tbl_out() )
                    ::heading:=.f.
                    ::outBuffer:=Hash()
        	case ::to==SQL_TO_TABLE
                    dbcreate(::filename,mstru)
                    use (::filename) new
			 	    __objModMethod( self, "newLine	", @tbl_newLine() ) 
				    __objModMethod( self,"out	", @tbl_out() )
                    ::heading:=.f.
                    ::outBuffer:=Hash()
        	case ::to==SQL_TO_ARRAY
		           	&(::filename):={}
			        //::arrayOutData:=&(::filename)
			 	    __objModMethod( self, "newLine	", @arr_newLine() ) 
				    __objModMethod( self,"out	", @arr_out() )
                    ::heading :=.f.
                    ::outBuffer  :={}
        	case ::to==SQL_TO_HASH
                	&(::filename):=Hash()
                    data:=&(::filename)
        			for each i in ::fields
						data[i:xname]:={}
                    next
			 	    __objModMethod( self, "newLine	", @hash_newLine() ) 
				    __objModMethod( self,"out	", @hash_out() )
                    ::heading :=.f.
                    ::outBuffer  :=Hash()
        	case ::to==SQL_TO_ANYHASH
			 	    __objModMethod( self, "newLine	", @anyhash_newLine() ) 
				    __objModMethod( self,"out	", @anyhash_out() )
                    ::heading :=.f.
                	::outBuffer  :=Hash()
        endcase
return Nil
**************************************************************
static function localSql_closeOut()
    Local  self:=HB_QSelf() 		

	if ::hFile!=NIL
    	fclose(::hFile)
		::hFile:=NIL
    endif
    if ::__sets!=NIL
    	restoreSets(::__sets)
    	::__sets:=NIL
    endif
return Nil
**************************************************************
**************************************************************
static function empty_newLine()
return Nil
**************************************************************
static function empty_out(colInfo,value)
return Nil
**************************************************************
**************************************************************
static function dev_newLine()
    Local  self:=HB_QSelf() 		

	::__curLine++
	if ::console .and. ::wait .and. ( ::__curLine % (maxrow()-1)==0 ) .and. ::__curLine>0
    	wait [Press any key to continue]
    endif
    if ::__curLine==0
    	if !::plain //.and. !::into
		?
    	endif
    else
    	? ::outBuffer
            ::outBuffer:=""
    endif
return Nil
**************************************************************
static function dev_out(colInfo,value)
    Local  self:=HB_QSelf() 		

	::outBuffer+=padr(toString(value,colInfo:len,colInfo:dec),colInfo:len)+" "
return Nil
**************************************************************
**************************************************************
static function file_newLine()
    Local  self:=HB_QSelf() 		

	::__curLine++
    if ::__curLine==0
    	if !::plain //.and. !::into
    		fwrite(::hfile,CRLF)
    	endif
    else
    	fwrite(::hfile,CRLF+::outBuffer)
        ::outBuffer:=""
    endif
return Nil
**************************************************************
static function file_out(colInfo,value)
    Local  self:=HB_QSelf() 		

	::outBuffer+=padr(toString(value,colInfo:len,colInfo:dec),colInfo:len)+" "
return Nil
**************************************************************
**************************************************************
static function tbl_newLine()
    Local  self:=HB_QSelf() 		
	Local _i

	if !empty(::outBuffer)
		append blank
		FOR EACH _i IN ::outBuffer:Keys
			FieldPut( FieldPos( _i ), ::outBuffer[_i] )	
		NEXT
    	::outBuffer:=Hash()
    endif
return Nil
**************************************************************
static function tbl_out(colInfo,value)
    Local  self:=HB_QSelf() 		
    
	///santy/? "outBuffer ",colInfo:xname," ",value
    ::outBuffer[colInfo:xname]:=value
return Nil
**************************************************************
**************************************************************
static function arr_newLine()
	Local s
    Local  self:=HB_QSelf() 		

	::__curLine++
    if ::__curLine<=0
    	return Nil
    endif
    s:="m->"+::filename
    s:=::filename
    //s:=&s
    aadd(&s,::outBuffer) //::arrayOutData
    ::outBuffer:={}
return Nil
**************************************************************
static function arr_out(colInfo,value)
    Local  self:=HB_QSelf() 		

	aadd(::outBuffer,value)
return Nil
**************************************************************
**************************************************************
static function hash_newLine()
	Local s,i
    Local  self:=HB_QSelf() 		

	::__curLine++
    if ::__curLine<=0
    	return Nil
    endif
    s:="m->"+::filename
    s:=&s
    for each i in s:KEYS
	  aadd(s[i],::outBuffer[i])
    next
    ::outBuffer:=Hash()
return Nil
**************************************************************
static function hash_out(colInfo,value)
    Local  self:=HB_QSelf() 		

    ::outBuffer[colInfo:xname]:=value
return Nil
**************************************************************
**************************************************************
static function anyhash_newLine(self)
	/*  	noting to do        */
return Nil
**************************************************************
static function anyhash_out(colInfo,value)
    Local  self:=HB_QSelf() 		

    ::outBuffer[value]:=value
return Nil
**************************************************************
static function localSql_append()
	Local i,j,m,tmp:={}
    Local  self:=HB_QSelf() 		

	if len(::groupBlocks)>0
        // get data for group fields
    	for each i in ::groupBlocks
               	aadd(tmp,eval(i))
         next
         // search group key and number of record
         ::__bufferLine:=0
         for i=1 to len(::groupKeys)
         	m:=::groupKeys[i]
          	for j=1 to len(tmp)
               	if ! (m[j]==tmp[j])
                  	exit
                endif
             next
             if j>len(tmp)
               	::__bufferLine:=i
                 exit
             endif
         next
         if ::__bufferLine==0
         	aadd(::groupKeys,tmp)
            aadd(::buffer,Hash())
            ::__bufferLine:=len(::groupKeys)
         endif
        else
        	if ::gFunctions
            	::__bufferLine:=1
                if len(::groupKeys)==0
                	aadd(::groupKeys,.t.)
                    aadd(::buffer,Hash() )
                endif
            else
            	//outlog(__FILE__,__LINE__,len(::groupKeys))//,::groupKeys)
                    aadd(::groupKeys,.t.)
            	//outlog(__FILE__,__LINE__,len(::groupKeys),len(::buffer))//,::groupKeys)
                    aadd(::buffer,Hash() )
            	//outlog(__FILE__,__LINE__,len(::buffer))//,::groupKeys)
            	::__bufferLine:=len(::groupKeys)
            	//outlog(__FILE__,__LINE__,len(::__bufferLine))//,::groupKeys)
            endif
        endif
        //outlog(__FILE__,__LINE__,len(::__bufferLine))//,::groupKeys)
return Nil
**************************************************************
static function localSql_toBuffer(colInfo,value)
    Local  self:=HB_QSelf() 		
		///santy/? "TO BUffer ",colInfo:xname," value ",value
        ::buffer[::__bufferLine][colInfo:xname]:=value
return Nil
**************************************************************
static function localSql_run()
    Local aname,i,ii,s,data
    Local _fldName:="",_fldName1:="",_vartmp:=""
    Local  self:=HB_QSelf() 		
    Local ___i

 	private __self:=self 

    if ::subQuery .and. !::externalData .and. !empty(::outBuffer)
    	return Nil
    endif

    for ii=1 to len(::from)
    	aname:=::from[ii]
        if !HB_IsNIL(::filters) 
            if !empty(::filters[ii])
    		  (aname:hDb)->(DBSetFilter(::filters[ii]))
            endif
        endif
		//? "hDB ",aname:hDB
        (aname:hDb)->(DbGotop())
        if ::functions  // create private variables for fields
   	      	FOR ___i:=1 to (aname:hDb)-> (FCount() )
		        _fldname1:=FieldName(___i)        		
		          PRIVATE &_fldname1
    	          __mvPut(_fldname1,fieldGet(___i))
	        NEXT 
        endif
    next
        
    ///santy/? "len(::fields) ",::fields[1]:aname," ",::fields[1]:xname," ",::fields[1]:fname
    for each s in ::fields
            //if hb_HHASKEY(s,"block")           
    		    data:=eval(s:block)
           		s:len:=max(s:len, max(IIF(HB_IsNumeric(data),LenNum(data),len(data)), ;
				iif(::to == SQL_TO_DBF,1,len(s:xname) ) ) )
				_vartmp := IIF(HB_IsNumeric(data),STR( data ), data)
				s:dec := IIF('.' $ _vartmp,MAX(LEN(_vartmp)-AT( '.', _vartmp ),s:dec),0)
            //endif
    next
	out_recursive(self,1)
	if !empty(::order)
		asort(::buffer,,,{|x1,x2|__localSql_order_by(::order,x1,x2)})
	endif
return Nil
**************************************************************
static function out_recursive(self,level)
    local data,idata,stru,i,j,maxLevel:=len(self:from)
    local hdb:=self:from[level]:hDB,_vartmp:=""
    local ___i
    memvar _fldname1

    (hdb)-> (DBGotop())
    while !(hdb) -> (Eof())
            if self:functions
   	        	FOR ___i:=1 to (hDb)-> (FCount() )
		          		_fldname1:=FieldName(___i)
		          		PRIVATE &_fldname1
    	          		__mvPut(_fldname1,fieldGet(___i))
	    	    	NEXT 
            endif
            if level==maxLevel .and. eval(self:filter) //.and. eval(self:hFilter)
    			self:append()
    			for each i in self:fields
					//if hb_HHASKEY(i,"block")           
                   	idata:=eval(i:block,i,self:__bufferLine)
                   	i:len:=max(IIF(HB_IsNumeric(idata),LenNum(idata),len(idata)),i:len)
					_vartmp := IIF(HB_IsNumeric(idata),STR( idata ),idata)
					i:dec := IIF('.' $ _vartmp,MAX(LEN(_vartmp)-AT( '.', _vartmp ),i:dec),0)
					self:toBuffer(i,idata)
					//endif
    			next
            endif
            if level<maxLevel
				out_recursive(self,Level+1)
            endif
           	(hdb) -> ( DbSkip() )
    enddo
return Nil
**************************************************************
static function localSql_outResult()
	local _i,iii,ii,s,data
    Local  self:=HB_QSelf() 
		
        /*
        	don`t remake result of query and return old data
                for subQueries as EXIST,ANY,ALL
        */
    if ::subQuery .and. !::externalData .and. !empty(::outBuffer)
      	return ::outBuffer
    endif

	::initOut()
    ::__havingProcess:=.t.

    if ::heading .and. !::plain //.and. !self:into
		::newLine()  // out header
    endif

    for each s in ::fields
      	s:enable:=.f.
		if ::heading .and. !::plain //.and. !self:into
	       	::out(s,s:xname)
    	endif
    next

	iii:=1
    ::__bufferLine:=0

    for each _i in ::buffer
      	::__bufferLine++
        if !eval(::hFilter,self,::__bufferLine)
   	       loop
        endif
       	::newLine()
      	for each ii in ::fields
			//if hb_HHASKEY(ii,"xname")           
           	::out(ii,_i[ii:xname])
			//endif
        next
    next

	if !empty(::outBuffer)
		::newLine()
    endif

	::closeOut()
    if ::subQuery
      	return ::outBuffer
    endif
return Nil
**************************************************************
static function localSql_getArrayData()
    Local  self:=HB_QSelf() 		
	Local _arr:={}

	IF (::to==SQL_TO_ARRAY)
		//? "LEN(::arrayOutData) ",LEN(::arrayOutData),"| ",::filename," | ",valtype(&(::filename))
       	//&(::filename):=::arrayOutData
		_arr:=::arrayOutData
	EndIf
Return _arr
*********************************************
* internal function for SQL query
*********************************************
function __localSql_sum(self,group_id,value)
	local i

    if !HB_IsHash(self) .or. HB_IsNIL(group_id) .or. group_id<=0
    	return 0.00
    endif

    if len(self:cnt_func)<group_id
           	for i=len(self:cnt_func) to group_id
            	aadd(self:cnt_func,0.00)
            next
    endif
	if HB_IsNumeric(value) .and. self:enable
        	self:cnt_func[group_id] += value
	endif
return self:cnt_func[group_id]
*********************************************
function __localSql_count(self,group_id,value)
	local i

    if !HB_IsHash(self) .or. HB_IsNIL(group_id) .or. group_id <= 0
    	return 0.00
    endif

    if len(self:cnt_func) < group_id
           	for i=len(self:cnt_func) to group_id
            	aadd(self:cnt_func,0.00)
            next
    endif
	//if valType(value)=="N" .and. self:enable
	if !empty(value) .and. self:enable
        	self:cnt_func[group_id]++
    endif
return self:cnt_func[group_id]
*********************************************
function __localSql_max(self,group_id,value)
	local i,_maxNumber:=0
	//Local  self:=HB_QSelf() 		

    if !HB_IsHash(self) .or. HB_IsNIL(group_id) .or. group_id<=0
    	return 0.00
    endif

    if len(self:cnt_func)<group_id
           	for i=len(self:cnt_func) to group_id
            	aadd(self:cnt_func,0.00-infinity())
            next
    endif

	if HB_IsNumeric(value) .and. self:enable
		AIns(self:cnt_func, group_id, MAX(self:cnt_func[group_id],value))
    endif
return self:cnt_func[group_id]
*********************************************
function __localSql_min(self,group_id,value)
	local i,_minNumber:=0

    if !HB_IsHash(self) .or. HB_IsNIL(group_id) .or. group_id<=0
    	return 0.00
    endif

    if len(self:cnt_func)<group_id
           	for i=len(self:cnt_func) to group_id
            	aadd(self:cnt_func,infinity())
            next
    endif

	if HB_IsNumeric(value) .and. self:enable
		//_minNumber:=MIN( self:min[group_id],value )
		self:cnt_func[group_id] := MIN( self:cnt_func[group_id],value )
	endif
return self:cnt_func[group_id]
*********************************************
function __localSql_avg(self,group_id,value)
	local i

    if !HB_IsHash(self) .or. HB_IsNIL(group_id) .or. group_id<=0
    	return 0.00
    endif
    if len(self:cnt_func) < group_id
           	for i=len(self:cnt_func) to group_id
            	aadd(self:cnt_func,0.00)
            next
    endif

    //if len(self:count_rec) < group_id
    //       	for i=len(self:count_rec) to group_id
    //        	aadd(self:count_rec,0.00)
    //        next
    //endif

	if HB_IsNumeric(value) .and. self:enable
        	self:cnt_func[group_id]+=value
			self:count_rec++
	endif

return self:cnt_func[group_id]/self:count_rec
*********************************************
static function fx_localSql_error(message,line)
	local i:=errorNew()

    Qout(__FILE__,__LINE__,procname(1),procline(1))
    Qout(__FILE__,__LINE__,procname(2),procline(2))
    Qout(__FILE__,__LINE__,procname(3),procline(3))
	i:description:="Sql syntax error "
    i:genCode:=EG_SYNTAX
    i:operation:=message
    i:filename:=__FILE__
    i:subSystem:="SQL"
    //i:subCode:=line
    break(i)
    //eval(errorblock(),i)
return Nil
*********************************************
static function __localSql_order_by(orderInfo,x1,x2)
	local i,c
	LOcal _i

    for each i in orderInfo
        c:=i:column
		if x1[c]==x2[c]
		else
    		if i:direction==SQL_DIRECTION_ASC
        		return x1[c]<x2[c]
        	else
        		return x1[c]>x2[c]
        	endif
		endif
    next
return .f.
*********************************************
function __localSql_fieldDesc(self,xname)
	local f

    for each f in self:fields
    	if f:xname == xname
           	return f
		endif
    next
return NIL
*********************************************
function __localSql_getValue(hDB,fname,xname,self)
	if !HB_IsNIL(self) .and. self:__havingProcess
        	return self:buffer[self:__bufferLine][xname]
    endif
return (hDb)-> ( FieldGet(FieldPos( fname )))
*********************************************
*********************************************
*********************************************
function __localSql_run_subQuery(data,value)
	local m, oper:=SQL_OPER_IN
    local i,ret:=.f.

	if HB_IsNIL(data) .or. empty(data)
        	return ret
    endif
        if valtype(data)=="O"
        	if "CLASSNAME" $ data .and. data:classname=="TSQLSELECT"
               	data:run()
               	m:=data:outResult()
                oper:=data:subQueryType
            else
               	m:=data
            endif
        else
        	return value==data
        endif

        do case
        	case oper==SQL_OPER_IN
               	ret:= ( value $ m)
               	for each i in m
                  	if i==value
                       	ret:=.t.	
						exit
                  	endif
                next
        	case oper==SQL_OPER_ANY
                	ret:= ( value $ m)
                	for each i in m
                        	if i==value
                            	ret:=.t.
								exit
							endif
                    next
        	case oper==SQL_OPER_SOME
                	ret:= ( value $ m)
                	for each i in m
                        	if i==value
                                ret:=.t.
                                exit
                            endif
                    next
        	case oper==SQL_OPER_EXIST
                	ret:= empty(m)
        	case oper==SQL_OPER_ALL
                	ret:=.t.
                        for each i in m
                        	if !(i==value)
                                ret:=.f.
                                exit
                            endif
                        next
        endcase
return ret
*********************************************
static function makeSqlSubSelect(cType,sQuery,value)
	local oQuery:=LocalSqlQueryNew():New()
	local tmp

    oQuery:subQuery:=.t.
    oQuery:subQueryType:=cType
    oQuery:parse(sQuery)
    oQuery:parseFrom()
    oQuery:parseTo()
    oQuery:openDB()
    oQuery:parseFields()
    oQuery:parseGroup()
    oQuery:parseOrder()
	//where 
    oQuery:where:=oQuery:parseExpr(oQuery:where)
    oQuery:where:=oQuery:logicalOptimizeExpr(oQuery:where)
    tmp:=oQuery:makeFilters(oQuery:where,.f.)
    oQuery:filter:=atail(tmp)
    asize(tmp,len(tmp)-1)
    oQuery:filters:=tmp
	//having 
    oQuery:having:=oQuery:parseExpr(oQuery:having)
    oQuery:having:=oQuery:logicalOptimizeExpr(oQuery:having)
    tmp:=oQuery:makeFilters(oQuery:having,.t.)
    oQuery:hFilter:=atail(tmp)

    oQuery:header:=.f.
    oQuery:to:=SQL_TO_ANYHASH

return oQuery
*********************************************
*********************************************
*********************************************
function tmpfile()
	local f:=""
	while .t.
		f="_tmp_harb"+alltrim(str(random(10000),4,0))
		f=strtran(f," ","0")
		if !file(f+".dbf") .and. !file(f+".fpt") .and. !file(f+".txt")
			exit
		endif
	enddo
return f
*********************************************
function restoreSets(m)
	local i
	if valtype(m)!="O"
		return .f.
	endif

	for each i in m:KEYS
	      set(i,m[i])
	next
return .t.
*********************************************
function saveSets()
	local i,__set_names:=setnames()
	for each i in __set_names:KEYS
	      __set_names[i]:=set(i)
	next
return __set_names
*********************************************
function toString( var,len,dec )
   local ret:="",sw:=valtype(var)
   do case
	  case sw=="A"
	   ret=LANG_ARRAY_STRING
	  case sw=="B"
	   ret=LANG_CBLOCK_STRING
	  case sw=="C"
	   ret=var
	  case sw=="D"
	   ret=dtoc(var)
	  case sw=="T"
	   ret=ttoc(var)
	  case sw=="L"
	   ret=iif(var,LANG_YES_STRING,LANG_NO_STRING)
	  case sw=="N"
	   ret=str(var,len,dec)
	  case sw=="M"
	   ret=LANG_MEMO_STRING
	  case sw=="O"
	   ret=LANG_OBJECT_STRING
	  case sw=="U"
	   ret=LANG_UNDEF_STRING
	  otherwise
	   ret=LANG_UNKNOWN_STRING
   end
return ret
*********************************************
function setnames()
	local __set_names
__set_names:=Hash()
__set_names[1]:=    "EXACT"
__set_names[2]:=    "FIXED"
__set_names[3]:=    "DECIMALS"
__set_names[4]:=    "DATEFORMAT"
__set_names[5]:=    "EPOCH"
__set_names[6]:=    "PATH"
__set_names[7]:=    "DEFAULT"
__set_names[8]:=    "EXCLUSIVE"
__set_names[9]:=    "SOFTSEEK"
__set_names[10]:=   "UNIQUE"
__set_names[11]:=   "DELETED"
__set_names[12]:=   "CANCEL"
__set_names[13]:=   "DEBUG"
__set_names[14]:=   "TYPEAHEAD"
__set_names[15]:=   "COLOR"
__set_names[16]:=   "CURSOR"
__set_names[17]:=   "CONSOLE"
__set_names[18]:=   "ALTERNATE"
__set_names[19]:=   "ALTFILE"
__set_names[20]:=   "DEVICE"
__set_names[21]:=   "EXTRA"
__set_names[22]:=   "EXTRAFILE"
__set_names[23]:=   "PRINTER"
__set_names[24]:=   "PRINTFILE"
__set_names[25]:=   "MARGIN"
__set_names[26]:=   "BELL"
__set_names[27]:=   "CONFIRM"
__set_names[28]:=   "ESCAPE"
__set_names[29]:=   "INSERT"
__set_names[30]:=   "EXIT"
__set_names[31]:=   "INTENSITY"
__set_names[32]:=   "SCOREBOARD"
__set_names[33]:=   "DELIMITERS"
__set_names[34]:=   "DELIMCHARS"
__set_names[35]:=   "WRAP"
__set_names[36]:=   "MESSAGE"
__set_names[37]:=   "MCENTER"
__set_names[38]:=   "SCROLLBREAK"
__set_names[39]:=   "EVENTMASK"
__set_names[40]:=   "VIDEOMODE"
__set_names[41]:=   "MBLOCKSIZE"
__set_names[42]:=   "MFILEEXT"
__set_names[43]:=   "STRICTREAD"
__set_names[44]:=   "OPTIMIZE"
__set_names[45]:=   "AUTOPEN"
__set_names[46]:=   "AUTORDER"
__set_names[47]:=   "AUTOSHARE"
__set_names[100]:=  "RATIONAL"
__set_names[101]:=  "TRANSLATE_PATH"
__set_names[102]:=  "FILECREATEMODE"
__set_names[103]:=  "DIRCREATEMODE"
__set_names[104]:=  "OPTIMIZELEVEL"
__set_names[105]:=  "LOCKSTYLE"
__set_names[106]:=  "MAPERR"
__set_names[107]:=  "DISPBOX"
__set_names[108]:=  "HOURS"
__set_names[109]:=  "SECONDS"
__set_names[110]:=  "MACRO_IN_STRING"
__set_names[111]:=  "FOPENMODE"
__set_names[112]:=  "BUFFERING"
__set_names[113]:=  "INDEX_BUFFER_LIMIT"
__set_names[114]:=  "MAP_FILE"
__set_names[115]:=  "ESC_DELAY"
__set_names[116]:=  "MULTILOCKS"
__set_names[117]:=  "FLUSHOUT"
__set_names[118]:=  "ROOTPATH"
__set_names[1000]:= "LOGLEVEL"
__set_names[1001]:= "LOGFILE"
return __set_names
*********************************************
function print_data(sData,sData1)
 ?""
 ?sData," ==== ",iif(empty(sData1),"",sData1)
 ?
Return nil
